1. Field of the Invention
This invention relates to database management systems and more particularly relates to binding and rebinding of query execution plans to database queries.
2. Description of the Related Art
Both critical and non-critical transactions rely on database technologies to store, retrieve, update, and delete data. Database management systems (DBMS) such as Oracle, DB2, IMS, MYSQL, are highly tuned and carefully managed to ensure that database queries to the DBMS are serviced and returned as quickly as possible. These queries maybe made using various languages including SQL, XPATH, and the like. The database queries may be dynamic (meaning the query is generated as needed, the query having a form that is unknown until the query is received and likely to change between requests) or static (meaning the database query is predefined does not change form between requests, although the data values of the query may change).
Typically, a DBMS will store the text representing a database query and/or assign a unique identifier for the database query. The database query is then associated with a query execution plan (QEP) or access path, also referred to as a query execution path. This association is referred to as a binding. The QEP identifies a set of indexes, tables, caches, and other optimization aides that the DBMS references each time the DBMS receives a request to execute the database query associated with the QEP. By following the QEP for a database query, a DBMS can significantly reduce response times.
A database administrator (DBA), or other user tasked with maintaining and tuning the DBMS, uses an assortment of tools and technologies to influence a DBMS like DB2 to generate a QEP that optimizes the use of DBMS resources and minimizes the response times for the static database query. Various tools and algorithms exist for identifying inefficient QEPs and determining new QEPs that are expected to perform better. Significant time and expense has been spent working to ensure that the new QEP is in fact more efficient than a previous QEP.
Unfortunately, these tools and algorithms are unable to provide a 100% guarantee that the new QEP for a particular database query, whether static or dynamic, is optimal. When a new QEP is bound to a database query in place of an original QEP, and the new QEP performs less optimally than the original QEP, the QEP binding for the database query has regressed. Regression of QEP bindings with consequential reduced performance can be very costly to an organization in terms of lost reputation, failure to meet quality of service obligations, operations disruptions, time and expense in troubleshooting the matter, and the like.
Some of these factors contributing to one QEP performing differently from another include differences in database accesses (typically whether a table is accessed by an index or not, and if so, which one), differences in how data is joined, the amount of query traffic, the variety of traffic loads, user request patterns, hardware configurations, processing bottlenecks, I/O bottlenecks, and the like. The problem of QEP binding regression is further complicated because the effects of binding a new QEP to a particular static database query may not be determinable within a predictable period of time due to usage patterns and the like. In addition, the configuration of the DBMS is such that administrative actions taken to improve performance of one set of static database queries can adversely affect another unknown set of static database queries.
Some solutions exist for identifying when a QEP is regressive and for computing a new QEP that is predicted to provide more optimal performance than an original QEP. Unfortunately, these solutions still require the DBA to review the proposed new QEP and decide whether to bind the database query to the new QEP.
The DBA may need to consider bindings of new QEPs for hundreds or thousands of database queries. The DBA must weigh the potential performance improvement against the potential that the new QEP is in fact not an improvement. The results of this decision are typically not known until the decision is made and the effects are experienced. Consequently, a DBA is often very reluctant to perform a rebinding without more proof that there will not be a regression in DBMS performance.
To minimize the impact of regression, a DBA will not perform the rebinding, or if the rebinding is performed, the DBA is prepared to quickly revert to original QEPs for a database query if performance suffers.